﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using MarketOnlineDTO;
using MySql.Data.MySqlClient;


namespace MarketOnlineDAO
{
    //@Võ Quang Trọng
    public class SanPhamDAO:BaseDataProvider
    {
        MySqlCommand _cmd;

        public List<SanPhamDTO> LayDanhSachSanPham()//tested
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham";
            _cmd.CommandType = CommandType.Text;

            DataTable dt = new DataTable();

            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            List<SanPhamDTO> list = new List<SanPhamDTO>();

            foreach (DataRow row in dt.Rows)
            {
                SanPhamDTO dto = new SanPhamDTO();
                dto.MaSP = int.Parse(row["MaSP"].ToString());
                dto.TenSP = row["TenSP"].ToString();
                dto.DonGia = Double.Parse(row["DonGia"].ToString());
                dto.MoTaChiTiet = row["MoTaChiTiet"].ToString();
                dto.HinhAnh = row["HinhAnh"].ToString();
                dto.MaTinhTrangSP = int.Parse(row["MaTinhTrangSP"].ToString());
                dto.MaLoaiSanPham = int.Parse(row["MaLoaiSP"].ToString());
                dto.MaDonViTinh = int.Parse(row["MaDonViTinh"].ToString());
                dto.SoLuongTon = Double.Parse(row["SoLuongTon"].ToString());

                list.Add(dto);
            }

            return list;
        }

        //Võ Quang Trọng - 5/3/2011
        public List<SanPhamDTO> LayDanhSachSanPhamMoi()//tested
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham order by MaSP desc limit 0,10";
            _cmd.CommandType = CommandType.Text;

            DataTable dt = new DataTable();

            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            List<SanPhamDTO> list = new List<SanPhamDTO>();

            foreach (DataRow row in dt.Rows)
            {
                SanPhamDTO dto = new SanPhamDTO();
                dto.MaSP = int.Parse(row["MaSP"].ToString());
                dto.TenSP = row["TenSP"].ToString();
                dto.DonGia = Double.Parse(row["DonGia"].ToString());
                dto.MoTaChiTiet = row["MoTaChiTiet"].ToString();
                dto.HinhAnh = row["HinhAnh"].ToString();
                dto.MaTinhTrangSP = int.Parse(row["MaTinhTrangSP"].ToString());
                dto.MaLoaiSanPham = int.Parse(row["MaLoaiSP"].ToString());
                dto.MaDonViTinh = int.Parse(row["MaDonViTinh"].ToString());
                dto.SoLuongTon = Double.Parse(row["SoLuongTon"].ToString());

                list.Add(dto);
            }

            return list;
        }

        public List<SanPhamDTO> LaySanPhamTheoLoai(int MaLoaiSP, int limit)
        {
            _cmd = CreateMysqlCommand();
            if (limit == 0)
            {
                _cmd.CommandText = "select * from SanPham where MaLoaiSP = @MaLoaiSP order by TenSP ASC";
            }
            else
            {
                _cmd.CommandText = "select * from SanPham where MaLoaiSP = @MaLoaiSP order by MaSP DESC limit 0,"+limit;
            }

            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@MaLoaiSP", MaLoaiSP));

            DataTable dt = new DataTable();

            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            List<SanPhamDTO> list = new List<SanPhamDTO>();

            foreach (DataRow row in dt.Rows)
            {
                SanPhamDTO dto = new SanPhamDTO();
                dto.MaSP = int.Parse(row["MaSP"].ToString());
                dto.TenSP = row["TenSP"].ToString();
                dto.DonGia = Double.Parse(row["DonGia"].ToString());
                dto.MoTaChiTiet = row["MoTaChiTiet"].ToString();
                dto.HinhAnh = row["HinhAnh"].ToString();
                dto.MaTinhTrangSP = int.Parse(row["MaTinhTrangSP"].ToString());
                dto.MaLoaiSanPham = int.Parse(row["MaLoaiSP"].ToString());
                dto.MaDonViTinh = int.Parse(row["MaDonViTinh"].ToString());
                dto.SoLuongTon = Double.Parse(row["SoLuongTon"].ToString());

                list.Add(dto);
            }

            return list;
        }        

        public List<SanPhamDTO> LayDanhSachSanPhamHetHang()//tested
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham where SoLuongTon=0";
            _cmd.CommandType = CommandType.Text;

            DataTable dt = new DataTable();

            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            List<SanPhamDTO> list = new List<SanPhamDTO>();

            foreach (DataRow row in dt.Rows)
            {
                SanPhamDTO dto = new SanPhamDTO();
                dto.MaSP = int.Parse(row["MaSP"].ToString());
                dto.TenSP = row["TenSP"].ToString();
                dto.DonGia = Double.Parse(row["DonGia"].ToString());
                dto.MoTaChiTiet = row["MoTaChiTiet"].ToString();
                dto.HinhAnh = row["HinhAnh"].ToString();
                dto.MaTinhTrangSP = int.Parse(row["MaTinhTrangSP"].ToString());
                dto.MaLoaiSanPham = int.Parse(row["MaLoaiSP"].ToString());
                dto.MaDonViTinh = int.Parse(row["MaDonViTinh"].ToString());
                dto.SoLuongTon = Double.Parse(row["SoLuongTon"].ToString());

                list.Add(dto);
            }

            return list;
        }


        public SanPhamDTO LaySanPham(int MaSanPham)//tested
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham where MaSP=@MaSanPham";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@MaSanPham", MaSanPham));

            DataTable dt = new DataTable();

            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            DataRow row = dt.Rows[0];
            
            SanPhamDTO dto = new SanPhamDTO();
            dto.MaSP = int.Parse(row["MaSP"].ToString());
            dto.TenSP = row["TenSP"].ToString();
            dto.DonGia = Double.Parse(row["DonGia"].ToString());
            dto.MoTaChiTiet = row["MoTaChiTiet"].ToString();
            dto.HinhAnh = row["HinhAnh"].ToString();
            dto.MaTinhTrangSP = int.Parse(row["MaTinhTrangSP"].ToString());
            dto.MaLoaiSanPham = int.Parse(row["MaLoaiSP"].ToString());
            dto.MaDonViTinh = int.Parse(row["MaDonViTinh"].ToString());
            dto.SoLuongTon = Double.Parse(row["SoLuongTon"].ToString());

                
            

            return dto;
        }

        public Boolean ThemSanPham(SanPhamDTO dto)
        {
            
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "insert into SanPham(TenSP, DonGia, MoTaChiTiet,HinhAnh, MaTinhTrangSP, MaLoaiSP, MaDonViTinh, SoLuongTon)";
            _cmd.CommandText += " values(@TenSP, @DonGia, @MoTaChiTiet,@HinhAnh, @MaTinhTrangSP, @MaLoaiSP, @MaDonViTinh, @SoLuongTon)";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@TenSP",dto.TenSP));
            _cmd.Parameters.Add(new MySqlParameter("@DonGia",dto.DonGia));
            _cmd.Parameters.Add(new MySqlParameter("@MoTaChiTiet",dto.MoTaChiTiet));
            _cmd.Parameters.Add(new MySqlParameter("@HinhAnh",dto.HinhAnh));
            _cmd.Parameters.Add(new MySqlParameter("@MaTinhTrangSP",dto.MaTinhTrangSP));
            _cmd.Parameters.Add(new MySqlParameter("@MaLoaiSP", dto.MaLoaiSanPham));
            _cmd.Parameters.Add(new MySqlParameter("@MaDonViTinh",dto.MaDonViTinh));
            _cmd.Parameters.Add(new MySqlParameter("@SoLuongTon",dto.SoLuongTon));

            if (ExecuteNonQuery(_cmd)==false)
            {
                return false;
            }
            
            return true;
        }

        public Boolean CapNhatSanPham(SanPhamDTO dto)
        {

            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "update SanPham set TenSP=@TenSP, DonGia=@DonGia, MoTaChiTiet=@MoTaChiTiet,HinhAnh=@HinhAnh, MaTinhTrangSP=@MaTinhTrangSP, MaLoaiSP=@MaLoaiSP, MaDonViTinh=@MaDonViTinh, SoLuongTon=@SoLuongTon";
            _cmd.CommandText += " where MaSP=@MaSP";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@TenSP", dto.TenSP));
            _cmd.Parameters.Add(new MySqlParameter("@DonGia", dto.DonGia));
            _cmd.Parameters.Add(new MySqlParameter("@MoTaChiTiet", dto.MoTaChiTiet));
            _cmd.Parameters.Add(new MySqlParameter("@HinhAnh", dto.HinhAnh));
            _cmd.Parameters.Add(new MySqlParameter("@MaTinhTrangSP", dto.MaTinhTrangSP));
            _cmd.Parameters.Add(new MySqlParameter("@MaLoaiSP", dto.MaLoaiSanPham));
            _cmd.Parameters.Add(new MySqlParameter("@MaDonViTinh", dto.MaDonViTinh));
            _cmd.Parameters.Add(new MySqlParameter("@SoLuongTon", dto.SoLuongTon));
            _cmd.Parameters.Add(new MySqlParameter("@MaSP", dto.MaSP));

            if (ExecuteNonQuery(_cmd) == false)
            {
                return false;
            }

            return true;
        }

        public Boolean XoaSanPham(int MaSanPham)
        {

            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "delete from SanPham";
            _cmd.CommandText += " where MaSP=@MaSP";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@MaSP", MaSanPham));

            if (ExecuteNonQuery(_cmd) == false)
            {
                return false;
            }

            return true;
        }
        //@Chung
        public List<SanPhamDTO> TimKiemSanPham(String key_search)
        {
            /*
           String[] M = key_search.Split(new char[] { ' ' }, StringSplitOptions.None);

           if (M.Length == 0)
               return null;

           
           String condition = "";

           for (int i = 0; i < M.Length; i++)
           { 
               condition  = condition + "TenSP like N'%"+M[i]+"%' or ";
           }
            */

            String condition = "TenSP like N'%" + key_search + "%' or ";
            condition = condition + "1=0";


            List<SanPhamDTO> LspDTO = new List<SanPhamDTO>();

            _cmd = CreateMysqlCommand();
            //_cmd.CommandText="select * from SanPham where TenSP like N'%"+key_search+"%'";
            _cmd.CommandText = "select * from SanPham where " + condition;
            _cmd.CommandType = CommandType.Text;
           

            DataTable dt = new DataTable();
            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count == 0)
            {
                return null;
            }

            int n = dt.Rows.Count;
            for (int i = 0; i < n; i++)
            {
                SanPhamDTO dto = new SanPhamDTO();
                dto.MaSP = int.Parse(dt.Rows[i][0].ToString());
                dto.TenSP = dt.Rows[i][1].ToString();
                dto.DonGia = Double.Parse(dt.Rows[i][2].ToString());
                dto.MoTaChiTiet = dt.Rows[i][3].ToString();
                dto.HinhAnh = dt.Rows[i][4].ToString();
                dto.MaTinhTrangSP = int.Parse(dt.Rows[i][5].ToString());
                dto.MaLoaiSanPham = int.Parse(dt.Rows[i][6].ToString());
                dto.MaDonViTinh = int.Parse(dt.Rows[i][7].ToString());
                dto.SoLuongTon = Double.Parse(dt.Rows[i][8].ToString());

                LspDTO.Add(dto);
            }
            return LspDTO;
        }
    }
}
